
const { query } = require('../db/mysql')

class Picture {
  constructor () {
    this.tableName = 'picture'
    this.picTagName = 'pic_tag'
    this.tagName = 'tag'
    this.downloadLog = 'pic_dl_log'
  }

  getById (params = {}) {
    const sql = `SELECT ${this.tableName}.id, ${this.tableName}.file_name, ${this.tableName}.title, ${this.tableName}.download, ${this.tableName}.publish_time, ${this.tableName}.exif, ${this.tableName}.tag
                  FROM ${this.tableName}
                    WHERE  ${this.tableName}.status = 1 AND ${this.tableName}.thumb = 1  AND ${this.tableName}.id = ?`
    return query(sql, [params.id])
  }

  getPictureByFileName (params = {}) {
    const sql = `SELECT ${this.tableName}.id
                   FROM ${this.tableName}
                     WHERE  ${this.tableName}.file_name = ?`
    return query(sql, [params.file_name])
  }


  getTabIdsByPicId (params = {}) {
    const sql = `SELECT tag_id
                  FROM ${this.picTagName}
                    WHERE  ${this.picTagName}.status = 1  and ${this.picTagName}.pic_id = ?`
    return query(sql, [params.id])
  }

  getRelateByPicId(params = {}) {
    const sql = `SELECT DISTINCT(${this.tableName}.id), ${this.tableName}.file_name as file_name, ${this.tableName}.download as download, ${this.tableName}.title as title, ${this.tableName}.publish_time as publish_time, ${this.tableName}.exif, ${this.tableName}.tag
                   FROM ${this.tableName} LEFT JOIN ${this.picTagName} 
                     ON ${this.tableName}.id = ${this.picTagName}.pic_id 
                       WHERE ${this.picTagName}.tag_id in (?) AND ${this.picTagName}.status = 1 AND ${this.tableName}.id <> ? AND ${this.tableName}.thumb = 1 AND ${this.tableName}.status = 1
                         ORDER BY ${this.tableName}.publish_time DESC
                           limit ?, ?`
    return query(sql, [params.tids, params.id, ((params.page - 1) * params.page_size), params.page_size])
  }

  getRelateByPicIdTotal(params = {}) {
    const sql = `SELECT COUNT(DISTINCT(picture.id)) AS total
                   FROM ${this.tableName} LEFT JOIN ${this.picTagName} 
                     ON ${this.tableName}.id = ${this.picTagName}.pic_id 
                       WHERE ${this.picTagName}.tag_id in (?) AND ${this.picTagName}.status = 1 AND ${this.tableName}.id <> ? AND ${this.tableName}.thumb = 1 AND ${this.tableName}.status = 1`
    return query(sql, [params.tids, params.id])
  }

  getPicActiveTags(params = {}) {
    const sql = `SELECT t.name, pt.tag_id, pt.tag_by, pt.pic_id, pt.confidence
                   FROM ${this.picTagName} pt JOIN ${this.tagName} t on t.id = pt.tag_id
                     WHERE pt.status=1 and pt.pic_id = ?`
    return query(sql, [params.id])
  }

  getList (params = {}) {
    const sql = `SELECT ${this.tableName}.id, ${this.tableName}.file_name, ${this.tableName}.title, ${this.tableName}.download, ${this.tableName}.publish_time, ${this.tableName}.exif, ${this.tableName}.tag
                   FROM ${this.tableName} WHERE ${this.tableName}.status = 1 AND ${this.tableName}.thumb = 1
                     ORDER BY ${this.tableName}.id DESC
                       LIMIT ?,?`
    return query(sql, [((params.page - 1) * params.page_size), params.page_size])
  }

  getListTotal (params = {}) {
    const sql = `SELECT COUNT(1) as total
                   FROM ${this.tableName} WHERE ${this.tableName}.status = 1 AND ${this.tableName}.thumb = 1`
    return query(sql, [((params.page - 1) * params.page_size), params.page_size])
  }

  getListByTagId (params = {}) {
    const sql = `SELECT ${this.tableName}.id, ${this.tableName}.file_name, ${this.tableName}.title, ${this.tableName}.download, ${this.tableName}.publish_time, ${this.picTagName}.tag_id as tag_id, ${this.tableName}.exif, ${this.tableName}.tag
                   from ${this.picTagName}  LEFT  JOIN ${this.tableName} 
                     ON ${this.tableName}.id = ${this.picTagName}.pic_id
                       WHERE ${this.picTagName}.tag_id = ? AND ${this.tableName}.thumb = 1 AND ${this.tableName}.status = 1
                         ORDER BY ${this.tableName}.publish_time DESC limit ?, ?`
    return query(sql, [params.tid, ((params.page - 1) * params.page_size), params.page_size])
  }

  getListByTagIdTotal (params = {}) {
    const sql = `SELECT count(1) as total
                   FROM ${this.picTagName} LEFT JOIN ${this.tableName} 
                     ON ${this.tableName}.id = ${this.picTagName}.pic_id
                       WHERE ${this.picTagName}.tag_id = ? AND ${this.tableName}.thumb = 1 AND ${this.tableName}.status = 1`
    return query(sql, [params.tid])
  }

  getListInPicTag (params = {}) {
    const sql = `SELECT ${this.tableName}.id, ${this.tableName}.file_name as file_name, ${this.tableName}.title as title, ${this.tableName}.download as download, ${this.tableName}.publish_time as publish_time, ${this.picTagName}.tag_id  as tag_id, ${this.tableName}.exif, ${this.tableName}.tag
                   FROM ${this.picTagName}  LEFT  JOIN ${this.tableName} 
                     ON ${this.tableName}.id = ${this.picTagName}.pic_id 
                        WHERE ${this.picTagName}.tag_id in(?) AND ${this.tableName}.thumb = 1 AND ${this.tableName}.status = 1 AND ${this.picTagName}.status = 1
                          GROUP BY ${this.tableName}.id  
                            ORDER BY ${this.tableName}.publish_time DESC 
                              LIMIT ?, ?`
    return query(sql, [params.tids, ((params.page - 1) * params.page_size), params.page_size])
  }

  getListInPicTagTotal (params = {}) {
    const sql = `SELECT count(DISTINCT(${this.tableName}.id)) as total
                   FROM ${this.picTagName}  LEFT  JOIN ${this.tableName} 
                     ON ${this.tableName}.id = ${this.picTagName}.pic_id 
                        WHERE ${this.picTagName}.tag_id in(?) AND ${this.tableName}.thumb = 1 AND ${this.tableName}.status = 1 AND ${this.picTagName}.status = 1`
    return query(sql, [params.tids])
  }

  getLatestTagList (params = {}) {
    // select pt.tag_id,tag.name from pic_tag pt join tag on tag.id=pt.tag_id where pt.status=1 and tag.`status`=1 and tag.name not like "%测试%" group by pt.tag_id order by pt.update_time limit 50;
    const sql = 'select pt.tag_id as id,tag.name as name from pic_tag pt join tag on tag.id=pt.tag_id where pt.status=1 and tag.`status`=1 and tag.name not like "%测试%" group by pt.tag_id order by pt.update_time desc limit 30'
    return query(sql)
  }

  getTagList (params = {}) {
    const sql = `SELECT ${this.tagName}.id, ${this.tagName}.name
                   FROM ${this.tagName} 
                     WHERE id in (?) `
    return query(sql, [params.ids])
  }


  updatePictureDownLoad (params = {}) {
    const sql = `UPDATE ${this.tableName} SET ${this.tableName}.download = ${this.tableName}.download + 1 WHERE ${this.tableName}.file_name = ?`
    return query(sql, [params.file_name])
  }

  
  insertPictureDownLoadLog (params = {}) {
    const sql = `INSERT INTO ${this.downloadLog} SET ?`
    return query(sql, [{ pid: params.pid, file_name: params.file_name, client_ip: params.client_ip }])
  }


}

module.exports = new Picture()
